package com.jdbc;

import java.sql.*;
import java.util.Scanner;

public class java1 {
        public static final String URL = "jdbc:mysql://localhost:3306/mydb?serverTimezone=GMT";
        public static final String USER = "root";
        public static final String PASSWORD = "1234";

    public static boolean login(String Username,String password){
        try(Connection connection = DriverManager.getConnection(URL,USER,PASSWORD)) {
            String sql = "select * from t_user where username = ? and password = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,Username);
            preparedStatement.setString(2,password);
            ResultSet resultSet = preparedStatement.executeQuery();
            return resultSet.next();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

        public static void StudentQuery() {
            try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
                Statement statement = connection.createStatement();
                String sql = "select * from student";
                ResultSet resultSet = statement.executeQuery(sql);
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    int age = resultSet.getInt("age");
                    String gender = resultSet.getString("gender");
                    String cellphone = resultSet.getString("cellphone");
                    String address = resultSet.getString("address");
                    System.out.println("id:" + id + "  name:" + name + "  age:" + age + "  gender:" + gender + "  cellphone:" + cellphone + "  address:" + address);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    public static void Studentinsert(String name,int age,String gender,String cellphone,String address){
        try(Connection connection = DriverManager.getConnection(URL,USER,PASSWORD);) {
            String sql = "insert into stu(name,age,gender,cellphone,address) values(?,?,?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            // 设置参数
            preparedStatement.setString(1,name);
            preparedStatement.setInt(2,age);
            preparedStatement.setString(3,gender);
            preparedStatement.setString(4,cellphone);
            preparedStatement.setString(5,address);
            // 执行命令
            int rows = preparedStatement.executeUpdate();
            if (rows > 0 ){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

        public static void Studentdelete(int id) {
            try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
                String sql = "DELETE FROM student WHERE id=?";
                PreparedStatement preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setInt(1, id);
                int rows = preparedStatement.executeUpdate();
                if (rows > 0 ){
                System.out.println("学生删除成功！");}
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        public static void Studentupdate(int id,String name,int age,String gender,String cellphone,String address) {
            try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
                String sql = "update stu set name = ?,age = ?,gender = ?,cellphone = ?,address = ? where id = ?";
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, name);
                preparedStatement.setInt(2, age);
                preparedStatement.setInt(3, id);
                preparedStatement.setString(4,gender);
                preparedStatement.setString(5,cellphone);
                preparedStatement.setString(6,address);
                int rows =preparedStatement.executeUpdate();
                if (rows > 0 ) {
                    System.out.println("学生更新成功！");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        public static void main(String[] args) {
            while (true) {
                System.out.println(" 1, 打印学生信息");
                System.out.println(" 2, 添加学生");
                System.out.println(" 3, 删除学生");
                System.out.println(" 4, 更新学生");
                System.out.println(" 5, 退出");
                Scanner sc = new Scanner(System.in);
                int choose = sc.nextInt();
                if (choose == 1) {
                    StudentQuery();
                }
                if (choose == 2) {
                    System.out.print("请输入要查询学生的信息:");
                    System.out.print("姓名:");
                    String name = sc.next();
                    System.out.print("年龄:");
                    int age = sc.nextInt();
                    System.out.print("性别:");
                    String gender = sc.next();
                    System.out.print("电话:");
                    String cellphone = sc.next();
                    System.out.print("住址:");
                    String address = sc.next();
                    Studentinsert(name, age, gender, cellphone, address);
                }
                if (choose == 3) {
                    System.out.println("请输入要删除学生的学号:");
                    int id = sc.nextInt();
                    Studentdelete(id);
                }
                if (choose == 4) {
                    System.out.println("请输入要更新学生的学号:");
                    int id = sc.nextInt();
                    System.out.println("请输入要学生更新后的信息:");
                    System.out.println("姓名:");
                    String name = sc.next();
                    System.out.println("年龄:");
                    int age = sc.nextInt();
                    System.out.println("性别:");
                    String gender = sc.next();
                    System.out.println("电话:");
                    String cellphone = sc.next();
                    System.out.println("住址:");
                    String address = sc.next();
                    Studentinsert(name, age, gender, cellphone, address);
                    Studentupdate(id, name, age, gender, cellphone, address);
                }
                if (choose == 5) {
                    return;
                }
            }
        }
    }

